﻿using System;

using System.Linq;
using System.Web;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for EmployeeDAL
/// </summary>

    public class CardTypeDAL:ICardTypeDAL
    {
        public CardTypeDAL()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        #region ICardTypeDAL Members

        /// <summary>
        /// GetAll
        /// </summary>
        /// <returns>List<CardType></returns>
        public  List<CardType> GetAll()
        {
            string strSQL = @"SELECT
                                CardTypeID,
                                [Name],
                                [Description],
                                E.FirstName,
                                E.LastName                             
                            FROM
                                CardType C
                            LEFT JOIN   
                                Employee E
                            ON E.EmployeeID=C.LastUser";
            SqlDataReader reader;
            List<CardType> list = null;
            CardType cardType=null;
            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
                if (reader.HasRows)
                {
                    list = new List<CardType>();
                    while (reader.Read())
                    {
                        cardType = new CardType();
                        cardType.Id = (Guid)reader["CardTypeID"];
                        cardType.Name = reader["Name"].ToString();
                        cardType.Description = reader["Description"].ToString();
                        Employee emp = new Employee();
                        emp.FirstName = reader["FirstName"].ToString();
                        emp.LastName = reader["LastName"].ToString();
                        cardType.Employee = emp;
                        list.Add(cardType);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<CardType> GetAllSearch()
        {
            string strSQL = @"SELECT
                                
                                CardID,
                                [Name]
                                                            
                            FROM
                                CardType C
                            LEFT JOIN   
                                Card E
                            ON E.CardTypeID=C.CardTypeID";
            SqlDataReader reader;
            List<CardType> list = null;
            CardType cardType = null;
            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
                if (reader.HasRows)
                {
                    list = new List<CardType>();
                    while (reader.Read())
                    {
                        cardType = new CardType();
                        
                        cardType.Name = SqlHelper.CheckStringNull(reader["Name"]);
                        cardType.CardID = SqlHelper.CheckStringNull(reader["CardID"]);
                        list.Add(cardType);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool InsertOrUpdate(CardType cardType)
        {

            #region Sql sentence
            string strSQLInsert = @"INSERT INTO [CardType] (
	                                    [CardTypeID],
	                                    [Name],
	                                    [Description],
	                                    [LastUser],
	                                    [LastUpdate]
                                    ) VALUES (
	                                    NewID(),
	                                    @Name,
	                                    @Description,
	                                    @LastUser,
	                                    GetDate()
                                    )";

            string strSQLUpdate = @"UPDATE [CardType] SET
	                                    [Name] = @Name,
	                                    [Description] = @Description,
	                                    [LastUser] = @LastUser,
	                                    [LastUpdate] = GetDate()
                                    WHERE
	                                    [CardTypeID] = @CardTypeID";
            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@CardTypeID",cardType.Id),
                        new SqlParameter("@Name",cardType.Name),
                        new SqlParameter("@Description",cardType.Description),
                        new SqlParameter("@LastUser",cardType.LastUser),
                    };

            string strSQL;
            //Insert case
            if (cardType.Id == Guid.Empty)
            {
                strSQL = strSQLInsert;
            }
            //delete case
            else
            {
                strSQL = strSQLUpdate;
            }

            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public CardType GetById(Guid id)
        {
            string strSQL = @"SELECT
	                            [CardTypeID],
	                            [Name],
	                            [Description]
                            FROM
	                            [CardType]
                            WHERE
	                            [CardTypeID] = @CardTypeID";
            SqlDataReader reader;
            CardType cardType = null;

            //parms
            SqlParameter parm = new SqlParameter("@CardTypeID", id);


            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parm);

                if (reader.HasRows)
                {
                    reader.Read();
                    cardType = new CardType();
                    cardType.Id = (Guid)reader["CardTypeID"];
                    cardType.Name = reader["Name"].ToString();
                    cardType.Description = reader["Description"].ToString();
                }
                return cardType;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool Delete(Guid id)
        {
            string strSQL = "DELETE FROM CardType WHERE CardTypeID=@CardTypeID";

            //parms
            SqlParameter parm = new SqlParameter("@CardTypeID", id);
            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parm) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool IsExistCardType(string name)
        {
            #region Sql sentence
            string strSQL = @"Select count(CardTypeID) from CardType WHERE Name=@Name";

            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@Name",name)
                    };
            try
            {
                return Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parms).ToString()) > 0;
            }
            catch (Exception)
            {
                throw;
            }
        } 
        #endregion
    }
